﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_GangguanTable
    /// </summary>
    public class CompanyReport_GangguanTable : CacheAbstraction
    {
        public DataTable GetGangguanRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetGangguanRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetGangguanRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_GANGGUAN_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxSelect =
                    "SUM(DECODE(IS_REPEATED_TROUBLE, 'Y', 1, 0)) as \"GANGGUAN BERULANG\", " +
                    "SUM(DECODE(IS_REPEATED_TROUBLE, 'T', 1, 0)) as \"BUKAN GANGGUAN BERULANG\", " +
                    "VALUE, " +
                    "MONTH, " +
                    "YEAR";
                String sxQuery = String.Format(
                    @"
                    select
                        {0}
                    from 
                        ( 
                            select 
                                TO_CHAR(a.TROUBLE_START_DATE_TIME, 'MON/YY') VALUE,
                                CAST(TO_CHAR(a.TROUBLE_START_DATE_TIME, 'MM') as number) MONTH,
                                CAST(TO_CHAR(a.TROUBLE_START_DATE_TIME, 'YYYY') as number) YEAR,
                                a.IS_REPEATED_TROUBLE
                            from
                                sni.t_trouble_record a    
                                left join sni.t_trouble_rec_crm b
                                    on a.T_TROUBLE_RECORD_ID = b.T_TROUBLE_RECORD_ID
                                left join sni.crm_trouble_report c
                                    on b.HOST_ID = c.HOST_ID
                                left join sni.network d
                                    on c.NETWORK_NO = d.NETWORK_NO
                                left join sni.network_site e
                                    on d.NETWORK_ID = e.NETWORK_ID
                                left join sni.site f
                                    on e.SITE_ID = f.SITE_ID
                            where
                                a.PARENT_RECORD_ID is null
                                and e.P_NETWORK_SITE_STATUS_ID = 4
                                and NVL(e.VALID_TO,sysdate+1) > sysdate
                                and f.COMPANY_ID = :prmID
                                and TRUNC(a.TROUBLE_START_DATE_TIME) >= TRUNC(:prmStart)
                                and TRUNC(a.TROUBLE_START_DATE_TIME) < TRUNC(:prmEnd + 1)
                        )
                    group by
                        VALUE,
                        MONTH,
                        YEAR
                    order by
                        YEAR,
                        MONTH 
                    ",sxSelect);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetGangguanDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetGangguanDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetGangguanDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_GANGGUAN_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxQuery =
                    @"
                    select 
                        c.NETWORK_NO,
                        a.TROUBLE_START_DATE_TIME, 
                        DECODE(a.IS_REPEATED_TROUBLE, 'Y', 'Ya', 'T', 'Tidak') REPEATED
                    from
                        sni.t_trouble_record a    
                        left join sni.t_trouble_rec_crm b
                            on a.T_TROUBLE_RECORD_ID = b.T_TROUBLE_RECORD_ID
                        left join sni.crm_trouble_report c
                            on b.HOST_ID = c.HOST_ID
                        left join sni.network d
                            on c.NETWORK_NO = d.NETWORK_NO
                        left join sni.network_site e
                            on d.NETWORK_ID = e.NETWORK_ID
                        left join sni.site f
                            on e.SITE_ID = f.SITE_ID
                    where
                        a.PARENT_RECORD_ID is null
                        and e.P_NETWORK_SITE_STATUS_ID = 4
                        and NVL(e.VALID_TO,sysdate+1) > sysdate
                        and f.COMPANY_ID = :prmID
                        and TRUNC(a.TROUBLE_START_DATE_TIME) > TRUNC(:prmStart - 1)
                        and TRUNC(a.TROUBLE_START_DATE_TIME) < TRUNC(:prmEnd + 1)
                    order by 
                        c.NETWORK_NO desc
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}